﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Data;

namespace Xiaov.SmartWeb.Application.TZ
{
  public class ExcelHelper
  {

    public string GetConn(string filePath, string type)
    {
      string connString = "";
      if (type == ".xlsx")
        connString = "Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source = " +
          filePath + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1;\"";
      else if (type == ".xls")
        connString = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " +
        filePath + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;\"";
      return connString;
    }

    public string[] GetSheets(string conn)
    {
      OleDbConnection myConn = new OleDbConnection(conn);
      myConn.Open();
      //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等
      DataTable dtSheetName = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
      //包含excel中表名的字符串数组
      string[] strTableNames = new string[dtSheetName.Rows.Count];
      for (int k = 0; k < dtSheetName.Rows.Count; k++)
      {
        strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
      }
      return strTableNames;
    }

    public DataTable GetExcelTables(string sheetName, string conn)
    {
      DataSet thisDataSet = new DataSet();
      try
      {
        OleDbConnection myConn = new OleDbConnection(conn);
        myConn.Open();
        OleDbDataAdapter thisAdapter = new OleDbDataAdapter("SELECT * FROM [" + sheetName + "]", myConn);
        //OleDbCommandBuilder thisBuilder = new OleDbCommandBuilder(thisAdapter);
        thisAdapter.Fill(thisDataSet, "ExcelInfo");
        myConn.Close();
      }
      catch (OleDbException ex)
      {
        //MessageBox.Show(ex.Message);
        throw new Exception(ex.Message);
      }
      return thisDataSet.Tables[0];
    }
  }
}